import pymysql
import pandas as pd
import os

mainDb = pymysql.connect(host='localhost', port=23306, user='root', password='Pr0dkcd_', db="kcdoc")
db_cursor = mainDb.cursor()


def clear_data():
    clear_sql = """
            DELETE FROM kcdoc_temp.lc_update_data
    """
    db_cursor.execute(clear_sql)
    mainDb.commit()


def insert_data_to_db(file_path, file_names):
    error_lc = []
    for file_name in file_names:
        file_name_ = file_path + file_name
        print(file_name_)
        data = pd.read_excel(file_name_, sheet_name='Sheet1', usecols=['流程编号', '工序序号', '工序名称', '更改后工时'])
        data.dropna(subset=['流程编号', '工序序号', '工序名称', '更改后工时'], inplace=True)
        insert_values = []
        for index, row in data.iterrows():
            if all(row.get(col) is not None for col in ['流程编号', '工序序号', '工序名称', '更改后工时']):
                insert_values.append((row['流程编号'], row['工序序号'], row['工序名称'], row['更改后工时']))
                if row.notna().all():
                    error_lc.append(row['流程编号'])

        # 批量插入数据
        if insert_values:
            insert_query = """
                INSERT INTO kcdoc_temp.lc_update_data (code, nuber, name, time) VALUES (%s, %s, %s, %s)
            """
            db_cursor.executemany(insert_query, insert_values)
            mainDb.commit()

    return error_lc


def update_data(error_lc):
    with open("C:\\Users\\xyh\\Desktop\\sql.txt", 'w') as file:
        for e in error_lc:
            update_info_1 = """
                      UPDATE kcdoc_temp.lc_update_data lud
                      LEFT JOIN kc_product_process_definition kppd ON lud.`code` = kppd.original_number
                      INNER JOIN kc_product_process_step kpps ON kppd.id = kpps.lcid AND kpps.pxh = lud.nuber AND kpps.gxmc = lud.`name`
                      SET kpps.time_out = lud.time
                      WHERE lud.`code` ='{0}' AND kppd.status = '0';
                  """.format(e)

            update_info_2 = """
                UPDATE  kc_production_plan_process ppp 
                inner join kcdoc.kc_production_plan kpp ON kpp.id = ppp.production_plan_id 
                inner join kc_product_process_definition ppd ON ppd.id = kpp.product_flow_id
                inner join kc_product_process_step pps ON ppd.id = pps.lcid and pps.id = ppp.stepId
                SET ppp.rated_work_time = pps.rated_work_time
                WHERE ppp.state = 0 AND ppd.original_number ='{}' AND ppd.`status` = 0
                AND ppp.time_out <> pps.time_out;
            """.format(e)
            file.write(update_info_1 + '\n')
            file.write(update_info_2 + '\n')




if __name__ == '__main__':
    file_path = 'D:\\woker_file\\709\\刷新工时\\'
    clear_data()
    file_names = os.listdir(file_path)
    filtered_file_names = [name for name in file_names if not name.startswith('~$')]
    if len(filtered_file_names) > 0:
        error_lc = insert_data_to_db(file_path, filtered_file_names)
        error_lc_ls = list(set(error_lc))
        update_data(error_lc_ls)
